* clean INE municipal registry data at census tract level for barcelona province
********************************************************************************
* prelims
********************************************************************************
{
clear all
set more off
cap log close

}
********************************************************************************
* clean
********************************************************************************
{
	* STEP 1: separate tables by gender
	****************************************************************************
	{
forval year = 2020(-1)2010 {

	if `year' >= 2011{
		local tables 01 02 03 04 05 06
	}
	else if (`year' > 2004 & `year' < 2011) {
		local tables 01 02 03 04 05
	}
	else if (`year' == 2004) {
		local tables 01 02 03
	}
	
forval prov = 8(1)8 {

	if `prov' < 10 {
		local pro = "0" + "`prov'"
	}
	else{
		local pro = "`prov'"
	}
	
	* tables
	foreach table in `tables' {

	* different format pre and post-2011
	if (`year' >= 2011) { 
		import excel "$orig/ine/census tracts/`year'/`pro'`table'.xlsx", sheet("tabla-0") clear
	}
	else if `year'==2009 {
		import excel "$orig/ine/census tracts/`year'/`table'`pro'.xlsx", sheet("tabla-0") clear
	
	}
	else if `year'==2005 {
		import excel "$orig/ine/census tracts/`year'/`table'_`pro'.xlsx", sheet("tabla-0") clear
	
	}
	else if (`year' > 2005 & `year' < 2011) & (`year'!=2009) {
		import delimited "$orig/ine/census tracts/`year'/`pro'`table'.csv", encoding(UTF-8) clear	
		rename v1 A
	}
	else if (`year' == 2004) { 
		import excel "$orig/ine/census tracts/`year'/`table'`pro'.xlsx", sheet("tabla-0") clear
	}
		
		g as = _n if (regexm(A,"[A]*[a]*mbos")==1)
		g ma = _n if (regexm(A,"[H]*[h]*ombre|[V]*[v]*ar[\ó]*[o]*n")==1)
		g fe = _n if (regexm(A,"[M]*[m]*uje")==1)

		foreach var in as ma fe {

			egen mean_`var' = mean(`var')
			drop `var'

		} 

		assert mean_as < mean_ma < mean_fe

		** three temp datasets
		* ambos sexos
		preserve
			keep if _n >=mean_as & _n < mean_ma
			compress 
			save "$data/temp/reg_tract_`year'_p`pro'_t`table'_as_1.dta", replace
		restore

		* male
		preserve
			keep if _n >=mean_ma & _n < mean_fe
			compress 
			save "$data/temp/reg_tract_`year'_p`pro'_t`table'_ma_1.dta", replace
		restore

		* female
		preserve
			keep if _n>=mean_fe
			compress 
			save "$data/temp/reg_tract_`year'_p`pro'_t`table'_fe_1.dta", replace
		restore

	} 
}

} 
	} 
	* STEP 2: clean each table-gender 
	****************************************************************************	
	{

forval year = 2020(-1)2010 {
	forval prov = 8(1)8 {

	if `prov' < 10 {
		local pro = "0" + "`prov'"
	}
	else{
		local pro = "`prov'"
	}

	foreach gender in as ma fe {
	
	
	****************************************************************************
	** TABLE 1: POPULATION BY AGE
	
		if `year' > 2007 {
	use "$data/temp/reg_tract_`year'_p`pro'_t01_`gender'_1.dta", clear
		}
		* before 2007 this is TABLE 02
		else if `year' <= 2007 {
	use "$data/temp/reg_tract_`year'_p`pro'_t02_`gender'_1.dta", clear	
		}
	
	if `year' < 2011 & `year'!=2004 & `year'!=2005 & `year'!=2009 {
	
	rename (v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12 v13 v14 v15 v16 v17 v18 v19 v20) (B C D E F G H I J K L M N O P Q R S T)
	cap drop v21
	
	}
	
	* clean
	drop if _n==1
	drop mean_*
	rename A CUSEC
	rename B pop
	rename C pop_0004
	rename D pop_0509
	rename E pop_1014
	rename F pop_1519
	rename G pop_2024
	rename H pop_2529
	rename I pop_3034
	rename J pop_3539
	rename K pop_4044
	rename L pop_4549
	rename M pop_5054
	rename N pop_5569
	rename O pop_6064
	rename P pop_6569
	rename Q pop_7074
	rename R pop_7579
	rename S pop_8084
	
	if `year' >= 2011 {
		
	rename T pop_8589
	rename U pop_9094
	rename V pop_9599
	rename W pop_100
	
	}
	else if `year' < 2011 {
	
	rename T pop_85
	
	}
	
	qui des, varlist
	local vars `r(varlist)'
	foreach var in `vars' {
		cap replace `var' = subinstr(`var',".","",.)
	}

	* gender
	g gender = "`gender'"
	g prov = `prov'

	* destring
	destring pop*, replace force

	* store clean
	compress
	save "$data/temp/reg_tract_`year'_p`pro'_t01_`gender'_2.dta", replace
	cap erase "$data/temp/reg_tract_`year'_p`pro'_t01_`gender'_1.dta"

	****************************************************************************
	** TABLE 2: NATIONALITY (CONTINENTS)
	
		if `year' > 2007 {
	use "$data/temp/reg_tract_`year'_p`pro'_t02_`gender'_1.dta", clear
		}
		else if `year' <= 2007 {
	use "$data/temp/reg_tract_`year'_p`pro'_t01_`gender'_1.dta", clear	
		}
		
	* clean
	drop if _n==1
	drop mean_*
	
		* before 2011 these are v vars (except 2004 2005 2009)
		if `year' <= 2010 & `year'!=2004 & `year'!=2005 & `year'!=2009 {
	
		rename (v2 v3 v4 v5 v6 v7 v8 v9) (B C D E F G H I)
		cap drop v10
	
		}
	
	* correct issue with character "."
	qui des, varlist
	local vars `r(varlist)'
	foreach var in `vars' {
		cap replace `var' = subinstr(`var',".","",.)
	}
	
	* destring
	destring B-I, force replace
	
	rename A CUSEC
	rename B pop
	rename C esp_nationality
	rename D foreign_nnt
	rename E africa_nnt
	rename F america_nnt
	rename G asia_nnt
	rename H europe_nnt
	rename I rest_nnt	
	
	* gender
	g gender = "`gender'"
	g prov = `prov'
	
	* drop temp vars
	cap drop J K
	
	* store
	compress
	save "$data/temp/reg_tract_`year'_p`pro'_t02_`gender'_2.dta", replace
	cap erase "$data/temp/reg_tract_`year'_p`pro'_t02_`gender'_1.dta"
	
	****************************************************************************
	** TABLE 3: NATIONALITY (MAIN NATIONALITIES)
	use "$data/temp/reg_tract_`year'_p`pro'_t03_`gender'_1.dta", clear

	drop if _n==1
	drop mean_*
	
	qui des, varlist
	local vars `r(varlist)'
	foreach var in `vars' {
		cap replace `var' = subinstr(`var',".","",.)
	}
	
	* clean each year
	* before 2011 these are v vars (except 2004 2005 2009)
	* 2019-2008 same countries
	if (`year'>2007 & `year' <= 2020) {
	
		* 2010 and 2008 are CSV files
		if (`year' == 2010 | `year'==2008) {
	
		#d;
			rename (v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12 v13 v14 v15 v16 v17 v18 
			v19 v20 v21 v22 v23 v24 v25 v26 v27 v28 v29 v30 v31 v32 v33 v34 v35 
			v36 v37 v38) 
			(B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE 
			AF AG AH AI AJ AK AL)
		;
		#d cr
		cap drop v39
	
		}
	
	destring B-AL, force replace
	rename A CUSEC
	rename B pop
	rename C esp_nationality
	rename D foreign_nnt
	rename E eu_nnt
	rename F deu_nnt
	rename G bul_nnt
	rename H fra_nnt
	rename I ita_nnt
	rename J pol_nnt
	rename K prt_nnt
	rename L gbr_nnt
	rename M rou_nnt
	rename N noneu_nnt
	rename O rus_nnt
	rename P ukr_nnt
	rename Q africa_nnt
	rename R dza_nnt
	rename S mar_nnt
	rename T nga_nnt
	rename U sen_nnt
	rename V america_nnt
	rename W arg_nnt
	rename X bol_nnt
	rename Y bra_nnt
	rename Z col_nnt
	rename AA cub_nnt
	rename AB chl_nnt
	rename AC ecu_nnt
	rename AD pry_nnt
	rename AE per_nnt
	rename AF dom_nnt
	rename AG ury_nnt
	rename AH ven_nnt
	rename AI asia_nnt
	rename AJ chn_nnt
	rename AK pak_nnt
	rename AL oceania_nnt
	
	}	
	else if `year' == 2007 {
	
		#d;
			rename (v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12 v13 v14 v15 v16 v17 v18 
			v19 v20 v21 v22 v23 v24 v25) 
			(B C D E F G H I J K L M N O P Q R S T U V W X Y)
		;
		#d cr
		cap drop v26
		
		
		destring B-Y, force replace
		rename A CUSEC
		rename B pop
		rename C esp_nationality
		rename D foreign_nnt
		rename E eu_nnt
		rename F deu_nnt
		rename G fra_nnt
		rename H ita_nnt
		rename I gbr_nnt
		rename J bul_nnt
		rename K rou_nnt
		rename L noneu_nnt
		rename M rus_nnt
		rename N ukr_nnt
		rename O africa_nnt
		rename P mar_nnt
		rename Q america_nnt
		rename R arg_nnt
		rename S bol_nnt
		rename T col_nnt
		rename U ecu_nnt
		rename V per_nnt
		rename W asia_nnt
		rename X chn_nnt
		rename Y oceania_nnt
		
	}
	* 2005-2006
	else if (`year' == 2006 |  `year' == 2005) {
	
		if `year' == 2006 {
			#d;
			rename (v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12 v13 v14 v15 v16 v17 v18 
			v19 v20 v21 v22 v23) 
			(B C D E F G H I J K L M N O P Q R S T U V W)
			;
			#d cr
			cap drop v24
		}
		
	
		destring B-W, force replace
		rename A CUSEC
		rename B pop
		rename C esp_nationality
		rename D foreign_nnt
		rename E eu_nnt
		rename F deu_nnt
		rename G fra_nnt
		rename H ita_nnt
		rename I gbr_nnt
		rename J noneu_nnt
		rename K bul_nnt
		rename L rou_nnt
		rename M africa_nnt
		rename N mar_nnt
		rename O america_nnt
		rename P arg_nnt
		rename Q bol_nnt
		rename R col_nnt
		rename S ecu_nnt
		rename T per_nnt
		rename U asia_nnt
		rename V chn_nnt
		rename W oceania_nnt
		
	}

	else if `year' == 2004 {
	
		destring B-R, force replace
		rename A CUSEC
		rename B pop
		rename C esp_nationality
		rename D foreign_nnt
		rename E eu_nnt
		rename F deu_nnt
		rename G fra_nnt
		rename H gbr_nnt
		rename I noneu_nnt
		rename J rou_nnt
		rename K africa_nnt
		rename L mar_nnt
		rename M america_nnt
		rename N col_nnt
		rename O ecu_nnt
		rename P arg_nnt
		rename Q asia_nnt
		rename R rest_nnt
		
	}
		
	g gender = "`gender'"
	g prov = `prov'
	
	* store
	compress
	save "$data/temp/reg_tract_`year'_p`pro'_t03_`gender'_2.dta", replace
	cap erase "$data/temp/reg_tract_`year'_p`pro'_t03_`gender'_1.dta"
	
	****************************************************************************
	** TABLE 4: NATIONALITY (EU AND NORWAY)
	* not available before 2004
	if `year' > 2004 {
	
	use "$data/temp/reg_tract_`year'_p`pro'_t04_`gender'_1.dta", clear

	drop if _n==1
	drop mean_*
	qui des, varlist
	local vars `r(varlist)'
	foreach var in `vars' {
		cap replace `var' = subinstr(`var',".","",.)
	}
	
	if `year' >= 2014 {
		
	destring B-AH, force replace
	rename A CUSEC
	rename B pop
	rename C esp_nationality
	rename D foreign_nnt
	rename E eu28_nor_nnt
	rename F deu_nnt
	rename G aut_nnt
	rename H bel_nnt
	rename I bul_nnt
	rename J cyp_nnt
	rename K hrv_nnt
	rename L dnk_nnt
	rename M svn_nnt
	rename N est_nnt
	rename O fin_nnt
	rename P fra_nnt
	rename Q grc_nnt
	rename R hun_nnt
	rename S irl_nnt
	rename T ita_nnt
	rename U lva_nnt
	rename V ltu_nnt
	rename W lux_nnt
	rename X mlt_nnt
	rename Y nld_nnt
	rename Z pol_nnt
	rename AA prt_nnt
	rename AB gbr_nnt
	rename AC cze_nnt
	rename AD svk_nnt
	rename AE rou_nnt
	rename AF swe_nnt
	rename AG nor_nnt
	rename AH resteurope_nnt
	
	}
	else if `year' >= 2011 & `year' < 2014 {
	
	destring B-AG, force replace
	rename A CUSEC
	rename B pop
	rename C esp_nationality
	rename D foreign_nnt
	rename E eu27_nor_nnt
	rename F deu_nnt
	rename G aut_nnt
	rename H bel_nnt
	rename I bul_nnt
	rename J cyp_nnt
	rename K dnk_nnt
	rename L svn_nnt
	rename M est_nnt
	rename N fin_nnt
	rename O fra_nnt
	rename P grc_nnt
	rename Q hun_nnt
	rename R irl_nnt
	rename S ita_nnt
	rename T lva_nnt
	rename U ltu_nnt
	rename V lux_nnt
	rename W mlt_nnt
	rename X nld_nnt
	rename Y pol_nnt
	rename Z prt_nnt
	rename AA gbr_nnt
	rename AB cze_nnt
	rename AC svk_nnt
	rename AD rou_nnt
	rename AE swe_nnt
	rename AF nor_nnt
	rename AG resteurope_nnt

	}
	else if `year' >= 2007 & `year' < 2011 {
	if (`year'==2010 | `year'==2008 | `year'==2007 ) {
		#d;
			rename (v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12 v13 v14 v15 v16 v17 v18 
			v19 v20 v21 v22 v23 v24 v25 v26 v27 v28 v29 v30 v31 v32 v33) 
			(B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE 
			AF AG)
		;
		#d cr
		cap drop v34
	}
		
	destring B-AG, force replace
	rename A CUSEC
	rename B pop
	rename C esp_nationality
	rename D foreign_nnt
	rename E eu27_nor_nnt
	rename F bel_nnt
	rename G dnk_nnt
	rename H deu_nnt
	rename I grc_nnt
	rename J fra_nnt
	rename K irl_nnt
	rename L ita_nnt
	rename M lux_nnt
	rename N nld_nnt
	rename O prt_nnt
	rename P gbr_nnt
	rename Q aut_nnt
	rename R fin_nnt
	rename S swe_nnt
	rename T cyp_nnt
	rename U svn_nnt
	rename V est_nnt
	rename W hun_nnt
	rename X lva_nnt
	rename Y ltu_nnt
	rename Z mlt_nnt
	rename AA pol_nnt
	rename AB cze_nnt
	rename AC svk_nnt
	rename AD bul_nnt
	rename AE rou_nnt
	rename AF nor_nnt
	rename AG resteurope_nnt
	
	}
	else if (`year'>=2005 & `year'<2007) {
	
	if (`year'==2006 ) {
		#d;
			rename (v2 v3 v4 v5 v6 v7 v8 v9 v10 v11 v12 v13 v14 v15 v16 v17 v18 
			v19 v20 v21 v22 v23 v24 v25 v26 v27 v28 v29 v30 v31) 
			(B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE)
		;
		#d cr
		cap drop v32
	}
	
	destring B-AE, force replace
	rename A CUSEC
	rename B pop
	rename C esp_nationality
	rename D foreign_nnt
	rename E eu25_nor_nnt
	rename F bel_nnt
	rename G dnk_nnt
	rename H deu_nnt
	rename I grc_nnt
	rename J fra_nnt
	rename K irl_nnt
	rename L ita_nnt
	rename M lux_nnt
	rename N nld_nnt
	rename O prt_nnt
	rename P gbr_nnt
	rename Q aut_nnt
	rename R fin_nnt
	rename S swe_nnt
	rename T cyp_nnt
	rename U svn_nnt
	rename V est_nnt
	rename W hun_nnt
	rename X lva_nnt
	rename Y ltu_nnt
	rename Z mlt_nnt
	rename AA pol_nnt
	rename AB cze_nnt
	rename AC svk_nnt
	rename AD nor_nnt
	rename AE resteurope_nnt
	
	}
	
	g gender = "`gender'"
	g prov = `prov'
	
	* store
	compress
	save "$data/temp/reg_tract_`year'_p`pro'_t04_`gender'_2.dta", replace
	cap erase "$data/temp/reg_tract_`year'_p`pro'_t04_`gender'_1.dta"
	
	****************************************************************************
	** TABLE 5: ORIGIN AND RESIDENCE
	
	use "$data/temp/reg_tract_`year'_p`pro'_t05_`gender'_1.dta", clear

	drop if _n==1
	drop mean_*	
	qui des, varlist
	local vars `r(varlist)'
	foreach var in `vars' {
		cap replace `var' = subinstr(`var',".","",.)
	}
	
	if `year' >= 2005 {
	
		if (`year'==2010|`year'==2008|`year'==2007|`year'==2006) {
		
		#d;
			rename (v2 v3 v4 v5 v6 v7 v8 v9 v10) 
			(B C D E F G H I J)
		;
		#d cr
		cap drop v11
	
		}
	
	destring B-J, force replace
	rename A CUSEC
	rename B pop
	rename C esp
	rename D same_reg
	rename E same_reg_pro
	rename F same_reg_pro_mun
	rename G same_reg_pro_dif_mun
	rename H same_reg_dif_pro
	rename I dif_reg
	rename J foreign
	cap drop K
	
	}
	
	g gender = "`gender'"
	g prov = `prov'
	
	cap drop K
	
	* store
	compress
	save "$data/temp/reg_tract_`year'_p`pro'_t05_`gender'_2.dta", replace
	cap erase "$data/temp/reg_tract_`year'_p`pro'_t05_`gender'_1.dta"
	
	} 
	****************************************************************************
	** TABLE 6: COUNTRY OF ORIGIN
	if `year'>=2011 {
	
	use "$data/temp/reg_tract_`year'_p`pro'_t06_`gender'_1.dta", clear
	
	drop if _n==1
	drop mean_*	
	qui des, varlist
	local vars `r(varlist)'
	foreach var in `vars' {
		cap replace `var' = subinstr(`var',".","",.)
	}
	
	destring B-AL, force replace		
	rename A CUSEC
	rename B pop
	rename C esp
	rename D foreign
	rename E eu
	rename F deu
	rename G bul
	rename H fra
	rename I ita
	rename J pol
	rename K prt
	rename L gbr
	rename M rou
	rename N noneu
	rename O rus
	rename P ukr
	rename Q africa
	rename R dza
	rename S mar
	rename T nga
	rename U sen
	rename V america
	rename W arg
	rename X bol
	rename Y bra
	rename Z col
	rename AA cub
	rename AB chl
	rename AC ecu
	rename AD pry
	rename AE per
	rename AF dom
	rename AG ury
	rename AH ven
	rename AI asia
	rename AJ chn
	rename AK pak
	rename AL oceania
	
	g gender = "`gender'"
	g prov = `prov'
	
	* store
	compress
	save "$data/temp/reg_tract_`year'_p`pro'_t06_`gender'_2.dta", replace
	cap erase "$data/temp/reg_tract_`year'_p`pro'_t06_`gender'_1.dta"
	
	} 

	} 
} 
}

	}
	* STEP 3: put together
	****************************************************************************
	{
* 2007-2005 missing three census tracts for provinces 08 and 28
forval year = 2020(-1)2010 {

	* different tables available in different years
	if `year' >= 2011{
		local tables 01 02 03 04 05 06
	}
	else if (`year' > 2004 & `year' < 2011) {
		local tables 01 02 03 04 05
	}
	else if (`year' == 2004) {
		local tables 01 02 03
	}


foreach table in `tables' {

clear

	forval prov = 8(1)8 {

		if `prov' < 10 {
			local pro = "0" + "`prov'"
		}
		else{
			local pro = "`prov'"
		}
	

		foreach gender in as ma fe {
			append using "$data/temp/reg_tract_`year'_p`pro'_t`table'_`gender'_2.dta"
			cap erase "$data/temp/reg_tract_`year'_p`pro'_t`table'_`gender'_2.dta"
		} 
	} 

	* store
	order CUSEC gender prov
	drop if pop==.
	compress
	save "$data/temp/reg_tract_`year'_t`table'.dta", replace

} 

* add other tables
clear
use "$data/temp/reg_tract_`year'_t01.dta", clear

	if `year' >= 2011{
		local tables2 02 03 04 05 06
	}
	else if (`year' > 2004 & `year' < 2011) {
		local tables2 02 03 04 05
	}
	else if (`year' == 2004) {
		local tables2 02 03
	}
	
foreach table in `tables2' {

	merge 1:1 CUSEC gender prov using "$data/temp/reg_tract_`year'_t`table'.dta"
	
	if (`year'!=2005 & `year'!=2006 & `year'!=2007) {
		assert _m==3
	}
	drop _m
}

	* finish cleaning year
	****************************************************************************
* remove white spaces from CUSEC
replace CUSEC = subinstr(CUSEC," ","",.)

* year
g year = `year'

* correction
replace CUSEC = "0" + CUSEC if prov == 8 & year <= 2007

* other variables
g CPRO = substr(CUSEC,1,2)
g CMUN = substr(CUSEC,3,3)
g CDIS = substr(CUSEC,6,2)
g CSEC = substr(CUSEC,8,3)

order C*

* label
la var year "year"
la var CUSEC "CUSEC"
la var CPRO "Province Code"
la var CMUN "Municipality Code"
la var CDIS "District Code"
la var CSEC "Section Code"
la var prov "Province"
la var pop "population"
la var gender "gender"
la var pop_0004 "population aged 0-4"
la var pop_0509 "population aged 5-9"
la var pop_1014 "population aged 10-14"
la var pop_1519 "population aged 15-19"
la var pop_2024 "population aged 20-24"
la var pop_2529 "population aged 25-29"
la var pop_3034 "population aged 30-34"
la var pop_3539 "population aged 35-39"
la var pop_4044 "population aged 40-44"
la var pop_4549 "population aged 45-49"
la var pop_5054 "population aged 50-54"
la var pop_5569 "population aged 55-59"
la var pop_6064 "population aged 60-64"
la var pop_6569 "population aged 65-69"
la var pop_7074 "population aged 70-74"
la var pop_7579 "population aged 75-79"
la var pop_8084 "population aged 80-84"
cap la var pop_8589 "population aged 85-89"
cap la var pop_9094 "population aged 90-94"
cap la var pop_9599 "population aged 95-99"
cap la var pop_100 "population aged 100+"
cap la var pop_85 "population aged 85+"
cap la var esp_nationality "spanish nationality"
cap la var foreign_nnt "foreign nationality (non-naturalized)"
cap la var africa_nnt "african nationality (non-naturalized)"
cap la var america_nnt "total america nnt"
cap la var asia_nnt "total asia nnt"
cap la var europe_nnt "total europe nnt"
cap la var rest_nnt "rest nnt"
cap la var eu_nnt "european union nnt"
cap la var deu_nnt "germany nnt"
cap la var bul_nnt "bulgaria nnt"
cap la var fra_nnt "france nnt"
cap la var ita_nnt "italy nnt"
cap la var pol_nnt "poland nnt"
cap la var prt_nnt "portugal nnt"
cap la var gbr_nnt "great britain nnt"
cap la var rou_nnt "romania nnt"
cap la var noneu_nnt "non-eu nnt"
cap la var rus_nnt "russia nnt"
cap la var ukr_nnt "ukraine nnt"
cap la var dza_nnt "algeria nnt"
cap la var mar_nnt "morocco nnt"
cap la var nga_nnt "nigeria nnt"
cap la var sen_nnt "senegal nnt"
cap la var arg_nnt "argentina nnt"
cap la var bol_nnt "bolivia nnt"
cap la var bra_nnt "brazil nnt"
cap la var col_nnt "colombia nnt"
cap la var cub_nnt "cuba nnt"
cap la var chl_nnt "chile nnt"
cap la var ecu_nnt "ecuador nnt"
cap la var pry_nnt "paraguay nnt"
cap la var per_nnt "peru nnt"
cap la var dom_nnt "dominican republic nnt"
cap la var ury_nnt "uruguay nnt"
cap la var ven_nnt "venezuela nnt"
cap la var chn_nnt "china nnt"
cap la var pak_nnt "pakistan nnt"
cap la var oceania_nnt "oceania and no-nation nnt"
cap la var eu28_nor_nnt "eu-28 and norway nnt"
cap la var eu27_nor_nnt "eu-27 and norway nnt"
cap la var eu25_nor_nnt "eu-25 and norway nnt"
cap la var aut_nnt "austria nnt"
cap la var bel_nnt "belgium nnt"
cap la var cyp_nnt "cyprus nnt"
cap la var hrv_nnt "croatia nnt"
cap la var dnk_nnt "denmark nnt"
cap la var svn_nnt "slovenia nnt"
cap la var est_nnt "estonia nnt"
cap la var fin_nnt "finland nnt"
cap la var grc_nnt "greece nnt"
cap la var hun_nnt "hungary nnt"
cap la var irl_nnt "ireland nnt"
cap la var lva_nnt "latvia nnt"
cap la var ltu_nnt "lithuania nnt"
cap la var lux_nnt "luxembourg nnt"
cap la var mlt_nnt "malta nnt"
cap la var nld_nnt "netherlands nnt"
cap la var cze_nnt "czech republic nnt"
cap la var svk_nnt "slovakia nnt"
cap la var swe_nnt "sweden nnt"
cap la var nor_nnt "norway nnt"
cap la var resteurope_nnt "rest europe nnt"
cap la var esp "spanish origin"
cap la var same_reg "born in same region"
cap la var same_reg_pro "born in same province"
cap la var same_reg_pro_mun "born in same municipality"
cap la var same_reg_pro_dif_mun "born in same province, diff municipality"
cap la var same_reg_dif_pro "born in same region, diff province"
cap la var dif_reg "born in different region"
cap la var foreign "foreign born"
cap la var eu "eu born"
cap la var deu "germany born"
cap la var bul "bulgaria born"
cap la var fra "france born"
cap la var ita "italy born"
cap la var pol "poland born"
cap la var prt "portugal born"
cap la var gbr "great britain born"
cap rename row rou 
cap la var rou "romania born"
cap la var noneu "europe non-eu born"
cap la var rus "russia born"
cap la var ukr "ukraine born"
cap la var africa "africa born"
cap la var dza "algeria born"
cap la var mar "morocco born"
cap la var nga "nigeria born"
cap la var sen "senegal born"
cap la var america "america born"
cap la var arg "argentina born"
cap la var bol "bolivia born"
cap la var bra "brazil born"
cap la var col "colombia born"
cap la var cub "cuba born"
cap la var chl "chile born"
cap la var ecu "ecuador born"
cap la var pry "paraguay born"
cap la var per "peru born"
cap la var dom "domician republic born"
cap la var ury "uruguay born"
cap la var ven "venezuela born"
cap la var asia "asia born"
cap la var chn "china born"
cap la var pak "pakistan born"
cap la var oceania "oceania and other born"

* store
order year C* gender prov
compress
save "$data/int/reg_csec_`year'.dta", replace

} 
	}
}
********************************************************************************
* put all years together
********************************************************************************
{
clear
forval year = 2020(-1)2010 {

	append using "$data/int/reg_csec_`year'.dta"

}

* final adjustments
replace pop_85 = pop_8589 + pop_9094 + pop_9599 + pop_100 if year >= 2011
replace eu27_nor_nnt = eu28_nor_nnt - hrv_nnt if eu27_nor_nnt==. 
cap replace eu25_nor_nnt = (eu28_nor_nnt - hrv_nnt - rou_nnt - bul_nnt) if eu25_nor_nnt==. 

* eu15
local toteu15 aut bel dnk fin fra deu grc irl ita lux nld prt swe gbr 
g eu15_nnt = 0
g eu15 = 0
la var eu15_nnt "eu15 nnt"
la var eu15 "born in eu15"
foreach var in `toteu15' {

replace eu15 = eu15 + `var'
replace eu15_nnt = eu15_nnt + `var'_nnt

}

* drop province aggregates
duplicates drop CUSEC gender year, force

drop if CPRO=="0T"

* store
compress
save "$data/int/reg_csec.dta", replace

}
********************************************************************************
* erase temp files
********************************************************************************
{
	
forval year = 2020(-1)2010 {
	forval prov = 8(1)8 {
		
	if `prov' < 10 {
		local pro = "0" + "`prov'"
	}
	else{
		local pro = "`prov'"
	}
		
	foreach table in 01 02 03 04 05 06 {
		foreach gender in ma fe as {
	
	
cap erase "$data/temp/reg_tract_`year'_p`pro'_t`table'_`gender'_1.dta"
cap erase "$data/temp/reg_tract_`year'_p`pro'_t`table'_`gender'_2.dta"	
cap erase "$data/temp/reg_tract_`year'_t`table'.dta"	

}
}
}	
}
	
}
********************************************************************************
* closing
********************************************************************************
{
cap log close
clear
}
